package com.portalEsporteClubeBahia.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.portalEsporteClubeBahia.app.ApplicationException;
import com.portalEsporteClubeBahia.app.vo.NewsVO;
import com.portalEsporteClubeBahia.app.vo.TypeNewsVO;

public class NewsDAO extends DAOFactory {

	public NewsDAO() throws ApplicationException {
		super();
	}
	
	public List<NewsVO> findAll() throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<NewsVO> list = new ArrayList<NewsVO>();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT TBL_NEWS.ID, TBL_TYPE_NEWS.ID, TBL_TYPE_NEWS.NAME, TBL_NEWS.TITLE, ");
			sql.append(" TBL_NEWS.DATE, TBL_NEWS.URLIMAGE, TBL_NEWS.CONTENT, TBL_NEWS.SHORTCONTENT, TBL_NEWS.URLWEBSITE, TBL_NEWS.RANKING ");
			sql.append(" FROM TBL_NEWS, TBL_TYPE_NEWS WHERE TBL_NEWS.TYPEID = TBL_TYPE_NEWS.ID AND active = 1 ");
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				NewsVO news = new NewsVO();
				news.setId(rs.getLong(1));
				news.setTypeNews(new TypeNewsVO());
				news.getTypeNews().setId(rs.getShort(2));
				news.getTypeNews().setName(rs.getString(3));
				news.setTitle(rs.getString(4));
				news.setDate(rs.getTimestamp(5));
				news.setUrlImage(rs.getString(6));
				news.setContent(rs.getString(7));
				news.setShortContent(rs.getString(8));
				news.setUrlWebSite(rs.getString(9));
				news.setRanking(rs.getInt(10));
				list.add(news);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public void updateRanking(NewsVO news) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("UPDATE TBL_NEWS SET RANKING = RANKING + 1 WHERE ID = ?");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, news.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public List<TypeNewsVO> findAllTypeNews() throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<TypeNewsVO> list = new ArrayList<TypeNewsVO>();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT * FROM TBL_TYPE_NEWS ORDER BY NAME ");
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				TypeNewsVO typeNews = new TypeNewsVO();
				typeNews.setId(rs.getShort("ID"));
				typeNews.setName(rs.getString("NAME"));
				list.add(typeNews);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public void insert(TypeNewsVO type) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO TBL_TYPE_NEWS ");
		sql.append(" (NAME) ");
		sql.append(" VALUES ");
		sql.append(" (?) ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, type.getName());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void update(TypeNewsVO type) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE TBL_TYPE_NEWS ");
		sql.append(" SET NAME = ? ");
		sql.append(" WHERE ");
		sql.append(" ID = ? ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, type.getName());
			ps.setShort(2, type.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void delete(TypeNewsVO type) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" DELETE FROM TBL_TYPE_NEWS WHERE ID = ?");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setShort(1, type.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void insert(NewsVO news) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO TBL_NEWS ");
		sql.append(" (TYPEID, TITLE, DATE, URLIMAGE, CONTENT, SHORTCONTENT, ACTIVE, URLWEBSITE) ");
		sql.append(" VALUES ");
		sql.append(" (?,?,?,?,?,?,?,?) ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setShort(1, news.getTypeNews().getId());
			ps.setString(2, news.getTitle());
			Timestamp ts = new Timestamp(news.getDate().getTime());
			ps.setTimestamp(3, ts);
			ps.setString(4, news.getUrlImage());
			ps.setString(5, news.getContent());
			ps.setString(6, news.getShortContent());
			ps.setBoolean(7, news.getActive());
			ps.setString(8, news.getUrlWebSite());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void update(NewsVO news) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE TBL_NEWS ");
		sql.append(" SET TYPEID = ?, TITLE = ?, DATE = ?, URLIMAGE = ?, CONTENT = ?, SHORTCONTENT = ?, ACTIVE = ?, URLWEBSITE = ? ");
		sql.append(" WHERE ");
		sql.append(" ID = ? ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setShort(1, news.getTypeNews().getId());
			ps.setString(2, news.getTitle());
			Timestamp ts = new Timestamp(news.getDate().getTime());
			ps.setTimestamp(3, ts);
			ps.setString(4, news.getUrlImage());
			ps.setString(5, news.getContent());
			ps.setString(6, news.getShortContent());
			ps.setBoolean(7, news.getActive());
			ps.setString(8, news.getUrlWebSite());
			ps.setLong(9, news.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void delete(NewsVO news) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" DELETE FROM TBL_NEWS WHERE ID = ? ");
		
		try{
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setLong(1, news.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public List<NewsVO> findByFilter(NewsVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<NewsVO> list = new ArrayList<NewsVO>();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT TBL_NEWS.ID, TBL_TYPE_NEWS.ID, TBL_TYPE_NEWS.NAME, TBL_NEWS.TITLE, ");
			sql.append(" TBL_NEWS.DATE, TBL_NEWS.URLIMAGE, TBL_NEWS.CONTENT, TBL_NEWS.SHORTCONTENT, TBL_NEWS.URLWEBSITE, TBL_NEWS.RANKING ");
			sql.append(" FROM TBL_NEWS, TBL_TYPE_NEWS WHERE TBL_NEWS.TYPEID = TBL_TYPE_NEWS.ID ");
			
			if(filter.getTitle()!=null && !filter.getTitle().equals("")){
				sql.append(" AND TBL_NEWS.TITLE LIKE '%"+filter.getTitle()+"%' ");
			}
			if(filter.getTypeNews()!=null && filter.getTypeNews().getId()!=null && filter.getTypeNews().getId()!=0){
				sql.append(" AND TBL_NEWS.TYPEID = "+filter.getTypeNews().getId()+" ");
			}
			if(filter.getShortContent()!=null && !filter.getShortContent().equals("")){
				sql.append(" AND TBL_NEWS.SHORTCONTENT LIKE '%"+filter.getShortContent()+"%' ");
			}
			if(filter.getUrlWebSite()!=null && !filter.getUrlWebSite().equals("")){
				sql.append(" AND TBL_NEWS.URLWEBSITE LIKE '%"+filter.getUrlWebSite()+"%' ");
			}
			if(filter.getDateStringStartsFormat()!=null && !filter.getDateStringStartsFormat().equalsIgnoreCase("")){
				sql.append(" AND TBL_NEWS.DATE >= '"+filter.getDateStringStartsFormat()+" 00:00:00' ");
			}
			if(filter.getDateStringEndsFormat()!=null && !filter.getDateStringEndsFormat().equalsIgnoreCase("")){
				sql.append(" AND TBL_NEWS.DATE <= '"+filter.getDateStringEndsFormat()+" 23:59:59' ");
			}
			sql.append(" AND TBL_NEWS.ACTIVE = "+filter.getActive());
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				NewsVO news = new NewsVO();
				news.setId(rs.getLong(1));
				news.setTypeNews(new TypeNewsVO());
				news.getTypeNews().setId(rs.getShort(2));
				news.getTypeNews().setName(rs.getString(3));
				news.setTitle(rs.getString(4));
				news.setDate(rs.getTimestamp(5));
				news.setUrlImage(rs.getString(6));
				news.setContent(rs.getString(7));
				news.setShortContent(rs.getString(8));
				news.setUrlWebSite(rs.getString(9));
				news.setRanking(rs.getInt(10));
				list.add(news);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public NewsVO findById(NewsVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		NewsVO news = new NewsVO();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT TBL_NEWS.ID, TBL_TYPE_NEWS.ID, TBL_TYPE_NEWS.NAME, TBL_NEWS.TITLE, ");
			sql.append(" TBL_NEWS.DATE, TBL_NEWS.URLIMAGE, TBL_NEWS.CONTENT, TBL_NEWS.SHORTCONTENT, TBL_NEWS.URLWEBSITE, TBL_NEWS.RANKING, TBL_NEWS.ACTIVE ");
			sql.append(" FROM TBL_NEWS, TBL_TYPE_NEWS WHERE TBL_NEWS.TYPEID = TBL_TYPE_NEWS.ID ");
			sql.append(" AND TBL_NEWS.ID = "+filter.getId());
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				news.setId(rs.getLong(1));
				news.setTypeNews(new TypeNewsVO());
				news.getTypeNews().setId(rs.getShort(2));
				news.getTypeNews().setName(rs.getString(3));
				news.setTitle(rs.getString(4));
				news.setDate(rs.getTimestamp(5));
				news.setUrlImage(rs.getString(6));
				news.setContent(rs.getString(7));
				news.setShortContent(rs.getString(8));
				news.setUrlWebSite(rs.getString(9));
				news.setRanking(rs.getInt(10));
				news.setActive(rs.getBoolean(11));
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return news;
		
	}
	
	public TypeNewsVO findById(TypeNewsVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TypeNewsVO type = new TypeNewsVO();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT ID, NAME FROM TBL_TYPE_NEWS WHERE ID = ?");
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setShort(1, filter.getId());
			rs = ps.executeQuery();
			
			while(rs.next()){
				type.setId(rs.getShort(1));
				type.setName(rs.getString(2));
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return type;
		
	}
	
	public List<TypeNewsVO> findByFilter(TypeNewsVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<TypeNewsVO> list = new ArrayList<TypeNewsVO>();
		
		try{
			StringBuffer sql = new StringBuffer(" SELECT ID, NAME FROM TBL_TYPE_NEWS ");
			if(filter.getName()!=null && !filter.getName().equalsIgnoreCase("")){
				sql.append(" WHERE NAME LIKE '%"+filter.getName()+"%'");
			}
			
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				TypeNewsVO type = new TypeNewsVO();
				type.setId(rs.getShort(1));
				type.setName(rs.getString(2));
				list.add(type);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}

}
